-- **************************************************************************
--    Project Name:   dm层 中转费月汇总表
--    Job Name:       spmi_dm.dm_transfer_income_pay_month_summary（分区字段：账单产生时间）
--    Description :   每次回算上个月的中转费账单，按照公斤段、不按公斤段维度；发件网点到目的城市粒度；做汇总。
--    Author :        yushuo
--    date：          2022/8/05
-- **************************************************************************
--    modify by yushuo 2022/08/05 上线
-- **************************************************************************

with t1 as (
    select a.dt
         , a.send_network_code
         , a.send_network_name
         , a.receiver_province_id
         , a.receiver_province_name
         , a.destination_code
         , a.destination_name
         , a.is_sign
         , a.fee_cost
         , a.fee_type_code
         , a.package_charge_weight -- 计费重量
         , case
               when a.package_charge_weight >= 0 and a.package_charge_weight <= 0.3 then 1
               when a.package_charge_weight > 0.3 and a.package_charge_weight <= 0.5 then 2
               when a.package_charge_weight > 0.5 and a.package_charge_weight <= 1 then 3
               when a.package_charge_weight > 1 and a.package_charge_weight <= 2 then 4
               when a.package_charge_weight > 2 and a.package_charge_weight <= 3.2 then 5
               when a.package_charge_weight > 3.2 and a.package_charge_weight <= 5.2 then 6
               when a.package_charge_weight > 5.2 and a.package_charge_weight <= 10.3 then 7
               when a.package_charge_weight > 10.3 then 8
               else 1 end kg_part  -- 没重量的、脏数据的 默认为0-0.3公斤段
    from spmi_dwd.dwd_spmi_transfer_bill_new_dt a
    where a.dt <= date_add(date_format(date_add('{{ execution_date | cst_ds }}',1),'yyyy-MM-01'),-1)
      and a.dt >= add_months(date_format(date_add('{{ execution_date | cst_ds }}',1),'yyyy-MM-01'),-1)
      and a.is_delete = 1
),
     t2 as (
         select date_format(add_months(date_add('{{ execution_date | cst_ds }}',1), -1), 'yyyy-MM-01') as bill_generation_month
              , date_add('{{ execution_date | cst_ds }}',1)                                            as update_dt
              , max(b.manage_code)                                          As sender_manage_code
              , max(b.manage_name)                                          As sender_manage_name
              , max(b.provider_id)                                          as sender_province_id
              , max(b.provider_desc)                                        as sender_province_name
              , max(b.financial_center_code)                                as send_financial_center_code
              , max(b.financial_center_desc)                                as send_financial_center_name
              , max(b.city_id)                                              as begin_code
              , max(b.city_desc)                                            as begin_name
              , max(b.fran_code)                                            as send_franchisee_code
              , max(b.fran_name)                                            as send_franchisee_name
              , a.send_network_code                                         as send_network_code
              , a.send_network_name                                         as send_network_name
              , max(b.business_model_id)                                    as business_model_id
              , max(b.business_model_desc)                                  as business_model_desc
              , '全国'                                                        as receiver_national
              , max(a.receiver_province_id)                                 as receiver_province_id
              , max(a.receiver_province_name)                               as receiver_province_name
              , a.destination_code                                          as destination_code
              , a.destination_name                                          as destination_name
              , cast(sum(`if`(a.fee_type_code = 'BF001'
             , 1
             , 0)) as decimal(21
             , 8))                                                          as sender_count
              , cast(sum(`if`(a.fee_type_code = 'BF001'
             , a.fee_cost
             , 0)) as decimal(21
             , 8))                                                          as accounts_sum
              , cast(sum(`if`(a.fee_type_code = 'BF001'
             , a.fee_cost
             , 0)) /
                     sum(`if`(a.fee_type_code = 'BF001'
                         , 1
                         , 0)) as decimal(21
             , 8))                                                          as accounts_avg
              , cast(sum(`if`(a.fee_type_code = 'BF001'
             , package_charge_weight
             , 0)) as decimal(21
             , 2))                                                          as sum_weight
              , cast(sum(`if`(a.fee_type_code = 'BF001'
             , package_charge_weight
             , 0)) /
                     sum(`if`(a.fee_type_code = 'BF001'
                         , 1
                         , 0)) as decimal(21
             , 2))                                                          as weight_avg
              , cast(sum(`if`(a.fee_type_code = 'BF003'
             , a.fee_cost
             , 0)) as decimal(21
             , 8))                                                          as cross_transfer_sum
              , cast(sum(`if`(a.fee_type_code = 'BF003'
             , 1
             , 0)) as decimal(21
             , 8))                                                          as cross_count
              , cast(sum(`if`(a.fee_type_code = 'BF003'
             , 1
             , 0)) /
                     sum(`if`(a.fee_type_code = 'BF001'
                         , 1
                         , 0)) as decimal(21
             , 8))                                                          as cross_avg_cnt
              , nvl(a.kg_part
             , 0)                                                           as kg_part
              , date_format(add_months(date_add('{{ execution_date | cst_ds }}',1), -1), 'yyyy-MM')    as mt
         from t1 a
                  left join jms_dim.dim_network_whole_massage b
                            on a.send_network_code = b.code
         group by a.send_network_code
                , a.send_network_name
                , a.destination_code
                , a.destination_name
                , a.kg_part
     )
insert overwrite table spmi_dm.dm_transfer_income_pay_month_summary partition(mt)
select *
from t2 distribute by mt, pmod(hash(rand()), 21);